﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace LANmine
{
    public partial class ManageTeams : Form
    {
        private User lmUser;
        private Language lmLang;
        private Database roDb = new Database("ro");
        private Database rwDb = new Database("rw");
        private DataSet ds = new DataSet();

        private UInt32 SelectedTeam;

        /// <summary>
        /// Standard constructor, shouldn't be used outside scope.
        /// </summary>
        private ManageTeams()
        {
            InitializeComponent();
        }

        /// <summary>
        /// Form to manage the user's teams. Calls the default constructor.
        /// </summary>
        /// <param name="lmUser">LANmine User Object</param>
        /// <param name="lmLang">LANmine Language Object</param>
        public ManageTeams(User lmUser, Language lmLang) : this()
        {
            //  Save in attribute 
            this.lmLang = lmLang;
            this.lmUser = lmUser;
        }

        #region Language Specifications
        
        /// <summary>
        /// Overwrite all text properties of all elements in all forms with the right ones.
        /// </summary>
        private void ApplyLanguage()
        {
            //  Window Title 
            this.Text = this.lmLang.GetString("compos.btnManageTeams");

            // Header
            this.lblManageTeams.Text = this.lmLang.GetString("compos.btnManageTeams");

            // Controls
            this.btnAddPlayer.Text = this.lmLang.GetString("mteam.btnAddPlayer");
            this.btnDelPlayer.Text = this.lmLang.GetString("mteam.btnDelPlayer");
            this.btnDelTeam.Text = this.lmLang.GetString("mteam.btnDelTeam");
            this.btnClose.Text = this.lmLang.GetString("mteam.btnClose");

        }

        #endregion

        #region Member Functions

        /// <summary>
        /// Called after the deletion of a team member.
        /// Disables the delete button if there are no members.
        /// </summary>
        private void FormStateMember()
        {
            // Disable Delete button if necessary
            if (lbxPlayers.Items.Count > 0)
                btnDelPlayer.Enabled = true;
            else
                btnDelPlayer.Enabled = false;
        }

        /// <summary>
        /// Called after the deletion of a team. Disables most form elements if there are no teams.
        /// </summary>
        private void FormStateTeam()
        {
            // Get the teams table
            DataTable dt = ds.Tables["teams"];

            if (dt.Rows.Count > 0)
            {
                //  DataTable is not empty, bind the control to the DataTable 
                cboTeam.DisplayMember = "naam";
                cboTeam.ValueMember = "id";
                cboTeam.DataSource = dt;
            }
            else
            {
                // Break the data binding
                cboTeam.DataSource = null;
                cboTeam.ValueMember = null;
                cboTeam.DisplayMember = null;

                //  Clear the listbox' contents 
                cboTeam.Items.Clear();

                //  Add an 'empty' row 
                cboTeam.Items.Add(lmLang.GetString("mteam.errNoTeams"));
                cboTeam.SelectedIndex = 0;

                //  Disable other controls on the form 
                cboTeam.Enabled = false;
                txtPlayer.Enabled = false;
                btnAddPlayer.Enabled = false;
                lbxPlayers.Enabled = false;
                btnDelPlayer.Enabled = false;
                btnDelTeam.Enabled = false;
            }
        }

        /// <summary>
        /// Gets a list of teams the user owns that aren't signed up for a competition.
        /// </summary>
        private void LoadTeams()
        {
            SqlConnection dbConn = this.roDb.getDbConn();
            SqlDataAdapter da = new SqlDataAdapter("lmGetOpenTeams", dbConn);

            try
            {
                //  Set as stored procedure 
                da.SelectCommand.CommandType = CommandType.StoredProcedure;

                //  Add Parameters 
                da.SelectCommand.Parameters.Add("@CaptId", SqlDbType.BigInt, 0);
                da.SelectCommand.Parameters["@CaptId"].Value = this.lmUser.ID;

                //  Fill the DataSet from the DataAdapter's table 
                da.Fill(ds, "teams");
            }
            catch (Exception ex)
            {
                //  Show error 
                MessageBox.Show(lmLang.GetString("mteam.errLoadTeams") + "\n" + ex.Message);
            }
        }

        /// <summary>
        /// Gets a list of members for the currently selected Team ID and
        /// manipulates the given ListBox.
        /// </summary>
        /// <param name="tId">Team ID to query DB with.</param>
        /// <param name="lbx">ListBox to manipulate.</param>
        private void LoadMembers(UInt32 tId, ListBox lbx)
        {
            SqlConnection dbConn = this.roDb.getDbConn();
            SqlDataAdapter da = new SqlDataAdapter("lmGetTeamMembers", dbConn);

            try
            {
                //  Set as stored procedure 
                da.SelectCommand.CommandType = CommandType.StoredProcedure;

                //  Add Parameters 
                da.SelectCommand.Parameters.Add("@TeamId", SqlDbType.BigInt, 0);
                da.SelectCommand.Parameters["@TeamId"].Value = tId;

                //  Clear the members table 
                if(ds.Tables["members"] != null)
                    ds.Tables["members"].Clear();

                //  Fill the DataSet from the DataAdapter's table 
                da.Fill(ds, "members");

                //  Do not overwrite the DataSource 
                if (lbx.DataSource == null)
                {
                    lbx.DataSource = ds.Tables["members"];
                    lbx.DisplayMember = "gebruikersnaam";
                    lbx.ValueMember = "id";
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(lmLang.GetString("mteam.errGetMembers") + "\n" + ex.Message);
            }
        }

        /// <summary>
        /// Gets a list of users in the system. This list is then added to the given StringCollection.
        /// </summary>
        /// <param name="data">StringCollection to fill with the data</param>
        private void LoadUsers(AutoCompleteStringCollection data)
        {
            SqlConnection dbConn = this.roDb.getDbConn();
            SqlDataAdapter da = new SqlDataAdapter("SELECT id, gebruikersnaam FROM deelnemers", dbConn);

            try
            {
                //  Clear the members table 
                if (ds.Tables["users"] != null)
                    ds.Tables["users"].Clear();

                //  Fill the DataSet's users table from the DataAdapter's table 
                da.Fill(ds, "users");

                //  Extract DataTable 
                DataTable dt = ds.Tables["users"];

                //  Loop over the results 
                foreach (DataRow r in dt.Rows)
                {
                    //  Add row to the StringCollection 
                    data.Add(r["gebruikersnaam"].ToString());
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(lmLang.GetString("mteam.errGetUsers") + "\n" + ex.Message);
            }
        }

        /// <summary>
        /// Add a player to the DataSet and push to the database.
        /// </summary>
        /// <param name="pUsr">The the UserName of the player to be added.</param>
        private void AddMember(String pUsr, UInt32 selTeam)
        {
            // Look up the given User Name in the users DataTable
            DataRow[] drUser = ds.Tables["users"].Select("gebruikersnaam = '" + pUsr + "'");

            // Request a new row
            DataRow newMem = ds.Tables["members"].NewRow();

            // Copy the row with the given username to the members DataRow
            newMem.ItemArray = drUser[0].ItemArray;

            // Add the row
            ds.Tables["members"].Rows.Add(newMem);

            // Create DataAdapter for the insert
            SqlDataAdapter daMem = new SqlDataAdapter();

            SqlCommand ins = new SqlCommand("lmAddTeamMember", rwDb.getDbConn());
            ins.CommandType = CommandType.StoredProcedure;

            // Map SProc parameters 
            ins.Parameters.Add("@UserId", SqlDbType.BigInt, 0, "id");
            ins.Parameters.Add("@TeamId", SqlDbType.BigInt, 0); // No value yet
            ins.Parameters["@TeamId"].Value = selTeam;
                
            // Set Insert Command
            daMem.InsertCommand = ins;

            // Send the members table back to the database
            daMem.Update(ds, "members");
        }

        /// <summary>
        /// Deletes a player from the selected team.
        /// </summary>
        /// <param name="dPlr">The player's name to delete, PK</param>
        /// <param name="selTeam">The currently selected team to delete from</param>
        private void DelMember(UInt32 dPlrId, UInt32 selTeam)
        {
            try
            {
                // Delete the found record from the table
                foreach (var r in ds.Tables["members"].Select("id = " + dPlrId))
                    r.Delete();

                // Create DataAdapter for the delete
                SqlDataAdapter daMem = new SqlDataAdapter();

                // Define an SQL command
                SqlCommand del = new SqlCommand("lmDelTeamMember", rwDb.getDbConn());
                del.CommandType = CommandType.StoredProcedure;

                // Map SQL parameters to the SQL command
                del.Parameters.Add("@UserId", SqlDbType.BigInt, 0, "id");
                del.Parameters.Add("@TeamId", SqlDbType.BigInt, 0); // No value yet
                del.Parameters["@TeamId"].Value = selTeam;

                // Set SqlCommand as the DataAdapter's delete command
                daMem.DeleteCommand = del;

                // Send the members table back to the database
                daMem.Update(ds, "members");
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// Deletes a team from the database.
        /// </summary>
        /// <param name="dTmId">The team ID to delete</param>
        private void DelTeam(UInt32 dTmId)
        {
            try
            {
                // Delete the current team, cannot fail if this function can be executed.
                foreach (var r in ds.Tables["teams"].Select("id = " + dTmId))
                    r.Delete();

                // Clear the members table, only contains data of the current team.
                ds.Tables["members"].Clear();

                // Create DataAdapter for the delete
                SqlDataAdapter daTeam = new SqlDataAdapter();

                // Define an SQL command
                SqlCommand del = new SqlCommand("lmDelTeam", rwDb.getDbConn());
                del.CommandType = CommandType.StoredProcedure;

                // Add SQL parameters to the SQL command
                del.Parameters.Add("@TeamId", SqlDbType.BigInt, 0, "id");

                // Set the SqlCommand as the DataAdapter's delete command
                daTeam.DeleteCommand = del;

                // Send the teams table back to the database
                daTeam.Update(ds, "teams");
            }
            catch (Exception)
            {
                throw;
            }
        }

        #endregion

        #region Event Handlers

        private void ManageTeams_Load(object sender, EventArgs e)
        {
            // Apply Language
            ApplyLanguage();

            // Load a list of teams into cboTeam 
            LoadTeams();

            // Set the form state
            FormStateTeam();

            //  txtUser AutoComplete feature 
            AutoCompleteStringCollection autoColl = new AutoCompleteStringCollection();
            LoadUsers(autoColl);
            this.txtPlayer.AutoCompleteCustomSource = autoColl;
            this.txtPlayer.AutoCompleteMode = AutoCompleteMode.Suggest;
        }

        private void cboTeam_SelectedIndexChanged(object sender, EventArgs e)
        {
            //  Check if we're dealing with bound data 
            if (cboTeam.SelectedItem is DataRowView)
            {
                //  Get the ID of the selected item 
                this.SelectedTeam = Convert.ToUInt32(cboTeam.SelectedValue.ToString());

                //  Load the members 
                LoadMembers(this.SelectedTeam, lbxPlayers);

                // Delete Button Status
                FormStateMember();
            }

            //  Clear the User field 
            this.txtPlayer.Clear();
        }

        private void txtPlayer_TextChanged(object sender, EventArgs e)
        {
            //  Look up the player in the DataSet 
            DataRow[] drUser = ds.Tables["users"].Select("gebruikersnaam = '" + txtPlayer.Text.ToString() + "'");
            DataRow[] drMember = ds.Tables["members"].Select("gebruikersnaam = '" + txtPlayer.Text.ToString() + "'");

            //  Check if the contents appear in the Users DataSet and NOT in the Members DataSet 
            if (drUser.Length > 0 && drMember.Length == 0 && txtPlayer.Text != lmUser.UserName)
                //  Enable the button 
                btnAddPlayer.Enabled = true;
            else
                //  Disable the button 
                btnAddPlayer.Enabled = false;
        }

        private void txtPlayer_KeyDown(object sender, KeyEventArgs e)
        {
            if (btnAddPlayer.Enabled == true && e.KeyCode == Keys.Enter)
            {
                btnAddPlayer_Click(sender, e);
            }
        }

        private void btnAddPlayer_Click(object sender, EventArgs e)
        {
            try
            {
                // Add the given player to the selected team
                AddMember(txtPlayer.Text, this.SelectedTeam);

                // Clear the username TextBox
                txtPlayer.Clear();

                // Delete Button Status
                FormStateMember();
            }
            catch (Exception ex)
            {
                MessageBox.Show(lmLang.GetString("mteam.errAddUser") + "\n" + ex.Message);
            }
        }

        private void btnDelPlayer_Click(object sender, EventArgs e)
        {
            // Get currently selected player
            UInt32 selMem = Convert.ToUInt32(this.lbxPlayers.SelectedValue.ToString());

            // Show confirmation dialog
            DialogResult dRes = MessageBox.Show(lmLang.GetString("mteam.qryDelMember"), lmLang.GetString("compos.btnManageTeams"), MessageBoxButtons.YesNo, MessageBoxIcon.Warning);

            if (dRes == DialogResult.Yes)
            {
                try
                {
                    // Remove the highlighted player from the dataset
                    DelMember(selMem, this.SelectedTeam);

                    // Delete Button Status
                    FormStateMember();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
            }
        }

        private void btnDelTeam_Click(object sender, EventArgs e)
        {
            // Show confirmation dialog
            DialogResult dRes = MessageBox.Show(lmLang.GetString("mteam.qryDelTeam"), lmLang.GetString("compos.btnManageTeams"), MessageBoxButtons.YesNo, MessageBoxIcon.Warning);

            if (dRes == DialogResult.Yes)
            {
                try
                {
                    // Delete the current team
                    DelTeam(this.SelectedTeam);

                    // Evaluate the form state
                    FormStateTeam();

                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
        }

        private void btnClose_Click(object sender, EventArgs e)
        {
            // Close the form
            this.Close();
        }

        #endregion


    }
}